package com.sinaapp.zygl.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.List;
import java.util.logging.Level;
import java.util.logging.Logger;

import com.sinaapp.zygl.bean.User;
import com.sinaapp.zygl.dao.UserDao;
import com.sinaapp.zygl.util.DateUtil;
import com.sinaapp.zygl.util.DbUtil;

public class UserDaoImpl implements UserDao {

	/**
	 * 注册
	 * 给User(name,pwd,type)
	 * @param user
	 */
	@Override
	public boolean register(User user) {
		Connection conn = DbUtil.getConnection();
		String sql = "insert into user(uid,name,pwd,type,createdatetime) values(?,?,?,?,?)";
		SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
		String date = sdf.format(new java.util.Date());
		try {
			PreparedStatement pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, String.valueOf(queryLastUID() + 1));
			pstmt.setString(2, user.getName());
			pstmt.setString(3, user.getPwd());
			pstmt.setString(4, user.getType());
			pstmt.setString(5, date);
			boolean a = checkUsername(user.getName());
			if (a) {
				System.out.print("此用户已存在");
			} else {
				pstmt.executeUpdate();
				return true;
			}
			System.out.println("用户已添加");
		} catch (SQLException e) {
			System.out.println("用户添加失败!");
		} finally {
			DbUtil.closeConnection(conn);
		}
		return false;
	}

	/**
	 * 登录
	 * @param user (name,pwd,type)
	 * @return user
	 */
	@Override
	public User login(User user) {
		Connection conn = DbUtil.getConnection();
		String sql = "select * from user where name =? and pwd=? and type=?";
		PreparedStatement pstmt;
		try {
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, user.getName());
			pstmt.setString(2, user.getPwd());
			pstmt.setString(3, user.getType());
			ResultSet rs = pstmt.executeQuery();
			if (rs.next()) {
				User user1 = new User();
				user1.setId(rs.getInt(1));
				user1.setUid(rs.getString(2));
				user1.setName(rs.getString(3));
				user1.setPwd(rs.getString(4));
				user1.setType(rs.getString(5));
				user1.setCreatedatetime(rs.getDate(6));
				user1.setModifydatetime(rs.getDate(7));
				return user1;// 登陆成功
			} else {
				return null;// 此用户不存在
			}
		} catch (SQLException ex) {
			Logger.getLogger(UserDaoImpl.class.getName()).log(Level.SEVERE, null, ex);
		} finally {
			DbUtil.closeConnection(conn);
		}
		return null;
	}

	/**
	 * 删除
	 * @param id
	 */
	@Override
	public boolean delete(int id) {
		Connection conn = DbUtil.getConnection();
		try {
			String sql = "delete from user where id=?";
			PreparedStatement pstmt = conn.prepareStatement(sql);
			pstmt.setInt(1, id);
			pstmt.executeUpdate();
			return true;
		} catch (SQLException ex) {
			Logger.getLogger(UserDaoImpl.class.getName()).log(Level.SEVERE, null, ex);
		} finally {
			DbUtil.closeConnection(conn);
		}
		return false;
	}

	/**
	 * 更新
	 * @param user (uid name pwd type createdatetime)
	 */
	@Override
	public boolean update(User user) {
		Connection conn = DbUtil.getConnection();
		String sql = "update user set name =?,pwd=?,type=?,createdatetime=?,modifydatetime=? where uid=? ";
		PreparedStatement pstmt;
		SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
		String date = sdf.format(new java.util.Date());
		try {
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, user.getName());
			pstmt.setString(2, user.getPwd());
			pstmt.setString(3, user.getType());
			pstmt.setString(4,
					DateUtil.dateToString(user.getCreatedatetime(), "yyyy-MM-dd HH:mm:ss"));
			pstmt.setString(5, date);
			pstmt.setString(6, user.getUid());
			pstmt.executeUpdate();
			return true;
		} catch (SQLException ex) {
			Logger.getLogger(UserDaoImpl.class.getName()).log(Level.SEVERE, null, ex);
		} finally {
			DbUtil.closeConnection(conn);
		}
		return false;
	}

	/**
	 * 查询记录总数 
	 * @param user 要查的where条件
	 * @return total
	 */
	@Override
	public int queryUserTotal(User u) {
		Connection conn = DbUtil.getConnection();
		String sql = addSqlString("select count(id) FROM user", u);
		int total = 0;
		try {
			Statement stmt = conn.createStatement();
			ResultSet rs = stmt.executeQuery(sql);
			if (rs.next()) {
				total = rs.getInt(1);
			}
		} catch (SQLException e) {
		} finally {
			DbUtil.closeConnection(conn);
		}
		return total;
	}

	/**
	 * 条件查询所有用户
	 * @param page 第几页
	 * @param rows 一次几条
	 * @param sort 通过什么字段排序(id uid name type)
	 * @param order 升序 降序(asc desc)
	 * @param user 要查的where条件
	 * @return userList
	 */
	@Override
	public List<User> getUsers(int page, int rows, String sort, String order, User user) {
		Connection conn = DbUtil.getConnection();
		List<User> queryuserlist = new ArrayList<User>();
		try {
			String sql1 = "";
			String sql2 = "";
			if ((sort == null || sort.equals("")) | (order == null || order.equals(""))) {
				sql2 = addSqlString("select * FROM user", user) + " ORDER BY id asc LIMIT 0,0";
				// System.out.println(sql2 + " "
				// + DateUtil.dateToString(new Date(), "yyyy-MM-dd HH:mm:ss"));
			} else {
				sql1 = " " + ((page - 1) * rows) + "," + rows + "";
				// select * FROM user ORDER BY id asc LIMIT 5,10
				sql2 = addSqlString("select * from user", user) + " order by " + sort + " " + order
						+ " " + "limit" + sql1;
				// System.out.println(sql2 + " "
				// + DateUtil.dateToString(new Date(), "yyyy-MM-dd HH:mm:ss"));
			}
			PreparedStatement pstmt = conn.prepareStatement(sql2);
			ResultSet rs = pstmt.executeQuery();
			while (rs.next()) {
				User user1 = new User();
				user1.setId(rs.getInt(1));
				user1.setUid(rs.getString(2));
				user1.setName(rs.getString(3));
				user1.setPwd(rs.getString(4));
				user1.setType(rs.getString(5));
				user1.setCreatedatetime(rs.getTimestamp(6));
				user1.setModifydatetime(rs.getTimestamp(7));
				queryuserlist.add(user1);
			}
			return queryuserlist;
		} catch (SQLException ex) {
			Logger.getLogger(UserDaoImpl.class.getName()).log(Level.SEVERE, null, ex);
		} finally {
			DbUtil.closeConnection(conn);
		}
		return null;
	}

	/**
	 * 拼接SQL字符串
	 * @param input 输入where之前的SQL语句
	 * @param u 要查询的对象
	 * @return 添加了where条件的SQL语句
	 */
	public String addSqlString(String input, User u) {
		String sql = input;
		SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
		String date = sdf.format(new java.util.Date());
		if ((u.getId() > 0) && ("" + u.getId()) != null) {
			// select * FROM user WHERE id ='5' ORDER BY id asc LIMIT 0,10
			sql += " where id=" + u.getId() + "";
			return sql;
		}
		if (u.getUid() != null && u.getUid().length() != 0) {
			// select * FROM user WHERE name like '土%%'or name like '%土%' ORDER
			// BY id asc LIMIT 0,10
			sql += " where uid like '" + u.getUid() + "%%' or uid like " + "'%" + u.getUid()
					+ "%' ";
			return sql;
		}
		if (u.getName() != null && u.getName().length() != 0) {
			// select * FROM user WHERE name like '土%%'or name like '%土%' ORDER
			// BY id asc LIMIT 0,10
			sql += " where name like '" + u.getName() + "%%' or name like " + "'%" + u.getName()
					+ "%' ";
			return sql;
		}
		if (u.getType() != null && u.getType().length() != 0) {
			sql += " where type like '" + u.getType() + "%%' or type like " + "'%" + u.getType()
					+ "%' ";
			return sql;
		}
		// 创建时间
		if ((u.getCreatedatetimeStart() != null) && (u.getCreatedatetimeEnd() == null)) {
			// select * FROM user WHERE createdatetime BETWEEN '2015-01-03
			// 00:00:00' AND
			// '2015-01-04 23:59:59' ORDER BY id asc LIMIT 0,10
			String s = DateUtil.dateToString(u.getCreatedatetimeStart(), "yyyy-MM-dd HH:mm:ss");
			sql += " where createdatetime  BETWEEN '" + s + "' and '" + date + "'";
			return sql;
		} else if ((u.getCreatedatetimeStart() != null) && (u.getCreatedatetimeEnd() != null)) {
			String s1 = DateUtil.dateToString(u.getCreatedatetimeStart(), "yyyy-MM-dd HH:mm:ss");
			String s2 = DateUtil.dateToString(u.getCreatedatetimeEnd(), "yyyy-MM-dd HH:mm:ss");
			sql += " where createdatetime  BETWEEN '" + s1 + "' and '" + s2 + "'";
			return sql;
		} else if ((u.getCreatedatetimeStart() == null) && (u.getCreatedatetimeEnd() != null)) {
			String s2 = DateUtil.dateToString(u.getCreatedatetimeEnd(), "yyyy-MM-dd HH:mm:ss");
			sql += " where createdatetime  BETWEEN '1970-01-01 00:00:00' and '" + s2 + "'";
			return sql;
		} else
		// 修改时间
		if ((u.getModifydatetimeStart() != null) && (u.getModifydatetimeEnd() == null)) {
			// select * FROM user WHERE Modifydatetime BETWEEN '2015-01-03
			// 00:00:00' AND
			// '2015-01-04 23:59:59' ORDER BY id asc LIMIT 0,10
			String s = DateUtil.dateToString(u.getModifydatetimeStart(), "yyyy-MM-dd HH:mm:ss");
			sql += " where Modifydatetime  BETWEEN '" + s + "' and '" + date + "'";
			return sql;
		} else if ((u.getModifydatetimeStart() != null) && (u.getModifydatetimeEnd() != null)) {
			String s1 = DateUtil.dateToString(u.getModifydatetimeStart(), "yyyy-MM-dd HH:mm:ss");
			String s2 = DateUtil.dateToString(u.getModifydatetimeEnd(), "yyyy-MM-dd HH:mm:ss");
			sql += " where Modifydatetime  BETWEEN '" + s1 + "' and '" + s2 + "'";
			return sql;
		} else if ((u.getModifydatetimeStart() == null) && (u.getModifydatetimeEnd() != null)) {
			String s2 = DateUtil.dateToString(u.getModifydatetimeEnd(), "yyyy-MM-dd HH:mm:ss");
			sql += " where Modifydatetime  BETWEEN '1970-01-01 00:00:00' and '" + s2 + "'";
			return sql;
		}

		return sql;
	}

	/**
	 * 查询用户是否存在
	 * @param username
	 * @return boolean
	 */
	public boolean checkUsername(String username) {
		Connection conn = DbUtil.getConnection();
		try {
			boolean b = false;
			String sql = "select * from user where name=?";
			PreparedStatement pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, username);
			ResultSet rs = pstmt.executeQuery();
			if (rs.next()) {
				return true;
			}
			return b;
		} catch (SQLException ex) {
			Logger.getLogger(UserDaoImpl.class.getName()).log(Level.SEVERE, null, ex);
		} finally {
			DbUtil.closeConnection(conn);
		}
		return false;
	}

	/**
	 * 查询最大的编号
	 * @return int
	 */
	public int queryLastUID() {
		Connection conn = DbUtil.getConnection();
		String sql = "select * from user order by uid desc";
		int useruid = 0;
		try {
			Statement stmt = conn.createStatement();
			ResultSet rs = stmt.executeQuery(sql);
			if (rs.next()) {
				useruid = Integer.parseInt(rs.getString(2));
			}
		} catch (SQLException e) {
		} finally {
			DbUtil.closeConnection(conn);
		}
		return useruid;
	}

	// /**
	// * 保存(估计用不到)
	// * 给User(uid,name,pwd,type,createdatetime)
	// * @param user
	// */
	// @Override
	// public void save(User user) {
	// DbUtil util = new DbUtil();
	// Connection conn = util.getConnection();
	// String sql =
	// "insert into user(uid,name,pwd,type,createdatetime) values(?,?,?,?,?)";
	// try {
	// PreparedStatement pstmt = conn.prepareStatement(sql);
	// pstmt.setString(1, user.getUid());
	// pstmt.setString(2, user.getName());
	// pstmt.setString(3, user.getPwd());
	// pstmt.setString(4, user.getType());
	// pstmt.setString(5,
	// DateUtil.dateToString(user.getCreatedatetime(), "yyyy-MM-dd HH:mm:ss"));
	// boolean a = checkUsername(user.getName());
	// if (a) {
	// System.out.print("此用户已存在");
	// } else {
	// pstmt.executeUpdate();
	// }
	// System.out.println("用户已添加");
	// } catch (SQLException e) {
	// System.out.println("用户添加失败!");
	// } finally {
	// util.closeConnection(conn);
	// }
	// }
}
